package com.F06.business;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Border;
import jxl.write.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.F06.action.F060101Action;
import com.F06.actionForm.F060101ActionForm;
import com.F06.dao.F060101DAO;
import com.action.BaseAction;
import com.util.ExcelExport;

public class F060101Business {

	public static void writeExcel(String modlePath, String outName,F060101DAO dao,F060101ActionForm actionForm) {
		/** 记录操作日志 */
		Logger log = Logger.getLogger(F060101Business.class);
		String time = actionForm.getSignaure_date_year()+"-"+actionForm.getSignaure_date_month()+"-1";
		log.info("time = "+time);
		WritableWorkbook wwb = null;
		Workbook workbook = null;
		List sheet0 = new ArrayList();
		List sheet1 = new ArrayList();
		List sheet2 = new ArrayList();
		Map sheetmap = new HashMap(); 
		Map sheetmap1 = new HashMap(); 
		Map sheetmap2 = new HashMap(); 
		String all = "all";// 单元格类型的参数
		String none = "none";// 单元格类型的参数
		String center = "center";// 单元格类型的参数
		try {
			try {
				// 根据模板去创建新的工作簿
				InputStream ins = new FileInputStream(modlePath);
				workbook = Workbook.getWorkbook(ins);
				File outFile = new File(outName);
				wwb = Workbook.createWorkbook(outFile, workbook);
			} catch (BiffException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
			if (wwb != null) {
				try {
					// 得到工作表第一页
					WritableSheet wsheet0 = wwb.getSheet(0);
					if (wsheet0 != null) {
						log.info(" wsheet0 " + wsheet0.getColumns());
						sheet0=dao.sheet0(time);
						if(sheet0!=null&&sheet0.size()>0){
							int sheet0hh = 7;
							for (int i = 0; i<sheet0.size(); i++) {
								sheetmap = (Map) sheet0.get(i);
								wsheet0.addCell(new Label(0, sheet0hh, sheetmap.get("订单号").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(1, sheet0hh, sheetmap.get("工作号").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(2, sheet0hh, sheetmap.get("技术代号").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(3, sheet0hh, sheetmap.get("型号").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(4, sheet0hh, sheetmap.get("规格").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(5, sheet0hh, sheetmap.get("数量").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(6, sheet0hh, sheetmap.get("合同单位1").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(7, sheet0hh, sheetmap.get("客户类别").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(8, sheet0hh, sheetmap.get("合同单位2").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(9, sheet0hh, sheetmap.get("单价").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(10, sheet0hh, sheetmap.get("合同合计不含税").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(11, sheet0hh, " ",ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(12, sheet0hh, sheetmap.get("期初数量").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(13, sheet0hh, " ",ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(14, sheet0hh, " ",ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(15, sheet0hh, " ",ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(16, sheet0hh, sheetmap.get("生产数量").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(17, sheet0hh, " ",ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(18, sheet0hh, " ",ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(19, sheet0hh, " ",ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(20, sheet0hh, sheetmap.get("生产成本").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(21, sheet0hh, " ",ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(22, sheet0hh, sheetmap.get("发票号码").toString(),ExcelExport.getSongTi(8,all)));
								wsheet0.addCell(new Label(23, sheet0hh, sheetmap.get("发票数量").toString(),ExcelExport.getSongTi(8,all)));
								sheet0hh = sheet0hh + 1;
							}
							log.info(" wsheet0 " + wsheet0.getColumns());
						}else {
							wsheet0.addCell(new Label(0, 8, "数据查询结果为空"));
						}
					//	wwb.write();
					} 
					// 得到工作表第二页
					WritableSheet wsheet1 = wwb.getSheet(1);
					if (wsheet1 != null) {
						log.info(" wsheet1 " + wsheet1.getColumns());
						sheet1=dao.sheet1(time);
						if(sheet1!=null&&sheet1.size()>0){
							int sheet1hh = 4;
							for (int i = 0; i < sheet1.size(); i++) {
								sheetmap1 = (Map) sheet1.get(i);
								wsheet1.addCell(new Label(0, sheet1hh, sheetmap1.get("月份").toString(),ExcelExport.getSongTi(8,all)));
								wsheet1.addCell(new Label(1, sheet1hh, sheetmap1.get("客户名称").toString(),ExcelExport.getSongTi(8,all)));
								wsheet1.addCell(new Label(2, sheet1hh, sheetmap1.get("工作号").toString(),ExcelExport.getSongTi(8,all)));
								wsheet1.addCell(new Label(3, sheet1hh, sheetmap1.get("产品型号").toString(),ExcelExport.getSongTi(8,all)));
								wsheet1.addCell(new Label(4, sheet1hh, sheetmap1.get("数量").toString(),ExcelExport.getSongTi(8,all)));
								wsheet1.addCell(new Label(5, sheet1hh, sheetmap1.get("单价").toString(),ExcelExport.getSongTi(8,all)));
								wsheet1.addCell(new Label(6, sheet1hh, sheetmap1.get("含税金额").toString(),ExcelExport.getSongTi(8,all)));
								wsheet1.addCell(new Label(7, sheet1hh, sheetmap1.get("不含税金额").toString(),ExcelExport.getSongTi(8,all)));
								wsheet1.addCell(new Label(8, sheet1hh, sheetmap1.get("开票日期").toString(),ExcelExport.getSongTi(8,all)));
								wsheet1.addCell(new Label(9, sheet1hh, sheetmap1.get("发票号").toString(),ExcelExport.getSongTi(8,all)));
								wsheet1.addCell(new Label(10, sheet1hh, sheetmap1.get("发货日期").toString(),ExcelExport.getSongTi(8,all)));
								wsheet1.addCell(new Label(11, sheet1hh, " ",ExcelExport.getSongTi(8,all)));
								sheet1hh = sheet1hh + 1;
							}
							log.info(" wsheet1 " + wsheet1.getColumns());
						}
						else {
								wsheet1.addCell(new Label(0, 8, "数据查询结果为空"));
						}
					}

					// 得到工作表第三页
					WritableSheet wsheet2 = wwb.getSheet(2);
					if (wsheet2 != null) {
						log.info(" wsheet2 " + wsheet2.getColumns());
						sheet2=dao.sheet2(time);
						if(sheet2!=null&sheet2.size()>0){
						int sheet2hh = 4;
						for (int i = 0; i < sheet2.size(); i++) {
							sheetmap2 = (Map) sheet2.get(i);
							wsheet2.addCell(new Label(0, sheet2hh, sheetmap2.get("客户名称").toString(),ExcelExport.getSongTi(8,all)));
							wsheet2.addCell(new Label(1, sheet2hh, sheetmap2.get("产品型号").toString(),ExcelExport.getSongTi(8,all)));
							wsheet2.addCell(new Label(2, sheet2hh, sheetmap2.get("月份").toString(),ExcelExport.getSongTi(8,all)));
							wsheet2.addCell(new Label(3, sheet2hh, sheetmap2.get("数量").toString(),ExcelExport.getSongTi(8,all)));
							wsheet2.addCell(new Label(4, sheet2hh, sheetmap2.get("单价").toString(),ExcelExport.getSongTi(8,all)));
							wsheet2.addCell(new Label(5, sheet2hh, sheetmap2.get("外购单价").toString(),ExcelExport.getSongTi(8,all)));
							wsheet2.addCell(new Label(6, sheet2hh, sheetmap2.get("外购收入不含税").toString(),ExcelExport.getSongTi(8,all)));
							wsheet2.addCell(new Label(7, sheet2hh, sheetmap2.get("外购成本含税").toString(),ExcelExport.getSongTi(8,all)));
							wsheet2.addCell(new Label(8, sheet2hh, sheetmap2.get("外购成本").toString(),ExcelExport.getSongTi(8,all)));
							sheet2hh = sheet2hh + 1;
						}
						log.info(" wsheet2 " + wsheet2.getColumns());
		                   
						}else {
							wsheet2.addCell(new Label(0, 8, "数据查询结果为空"));
						}
					} 
					wwb.write();
					actionForm.setErrorFlag("7");
				} catch (RowsExceededException e) {
					actionForm.setErrorFlag("6");
					e.printStackTrace();
				} catch (WriteException e) {
					actionForm.setErrorFlag("6");
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			System.out.print("写入Excel出错");
			e.printStackTrace();
		} finally {
			try {
				workbook.close();
				wwb.close();
			} catch (WriteException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * Times New Roman 设置excel字体及单元格样式
	 * 
	 * @param size
	 *            字体大小
	 * @param style
	 *            单元格样式
	 * @return
	 */
	public static WritableCellFormat getNewRoman(int size, String style) {
		WritableFont wf = new WritableFont(WritableFont
				.createFont("Times New Roman"), size, WritableFont.NO_BOLD,
				false);
		WritableCellFormat wcfF = new WritableCellFormat(wf);
		try {
			wcfF.setAlignment(Alignment.RIGHT);
			/** 把水平对齐方式指定为居右 */
			wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);
			/** 把垂直对齐方式指定为居中 */
			// wcfF.setBackground(Colour.WHITE);
			wcfF.setWrap(true);
			if ("all".equals(style)) {
				wcfF.setBorder(Border.TOP, BorderLineStyle.THIN);
				/** 把单元格上边框样式指定加重 */
				wcfF.setBorder(Border.LEFT, BorderLineStyle.THIN);
				/** 把单元格左边框样式指定加重 */
				wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN);
				/** 把单元格右侧边框样式指定为加重 */
				wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
				/** 把单元格底边框样式指定加重 */
			} else if ("none".equals(style)) {
				// 单元格无边框
				wcfF.setBorder(Border.TOP, BorderLineStyle.NONE);
				/** 把单元格上边框样式指定虚线 */
				wcfF.setBorder(Border.LEFT, BorderLineStyle.NONE);
				/** 把单元格左边框样式指定虚线 */
				wcfF.setBorder(Border.RIGHT, BorderLineStyle.NONE);
				/** 把单元格右侧边框样式指定为虚线 */
				wcfF.setBorder(Border.BOTTOM, BorderLineStyle.NONE);
				/** 把单元格底边框样式指定虚线 */
			} else if ("center".equals(style)) {
				wcfF.setAlignment(Alignment.CENTRE);
				/** 把水平对齐方式指定为居右 */
				// 单元格有边框
				wcfF.setBorder(Border.TOP, BorderLineStyle.THIN);
				/** 把单元格上边框样式指定加重 */
				wcfF.setBorder(Border.LEFT, BorderLineStyle.THIN);
				/** 把单元格左边框样式指定加重 */
				wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN);
				/** 把单元格右侧边框样式指定为加重 */
				wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
				/** 把单元格底边框样式指定加重 */
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return wcfF;
	}

	/**
	 * 宋体 设置excel字体及单元格样式
	 * 
	 * @param size
	 *            字体大小
	 * @param style
	 *            单元格样式
	 * @return
	 */
	public static WritableCellFormat getSongTi(int size, String style) {
		WritableFont wf = new WritableFont(WritableFont.createFont("宋体"), size,
				WritableFont.NO_BOLD, false);
		WritableCellFormat wcfF = new WritableCellFormat(wf);
		try {
			wcfF.setAlignment(Alignment.LEFT);
			/** 把水平对齐方式指定为居左 */
			wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);
			/** 把垂直对齐方式指定为居中 */
			wcfF.setBackground(Colour.WHITE);
			wcfF.setWrap(true);
			if ("all".equals(style)) {
				wcfF.setBorder(Border.TOP, BorderLineStyle.THIN);
				/** 把单元格上边框样式指定加重 */
				wcfF.setBorder(Border.LEFT, BorderLineStyle.THIN);
				/** 把单元格左边框样式指定加重 */
				wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN);
				/** 把单元格右侧边框样式指定为加重 */
				wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
				/** 把单元格底边框样式指定加重 */
			} else if ("none".equals(style)) {
				// 单元格无边框
				wcfF.setBorder(Border.TOP, BorderLineStyle.NONE);
				/** 把单元格上边框样式指定虚线 */
				wcfF.setBorder(Border.LEFT, BorderLineStyle.NONE);
				/** 把单元格左边框样式指定虚线 */
				wcfF.setBorder(Border.RIGHT, BorderLineStyle.NONE);
				/** 把单元格右侧边框样式指定为虚线 */
				wcfF.setBorder(Border.BOTTOM, BorderLineStyle.NONE);
				/** 把单元格底边框样式指定虚线 */
			} else if ("title".equals(style)) {
				wcfF.setAlignment(Alignment.CENTRE);
				/** 把水平对齐方式指定为居中 */
				wcfF.setBorder(Border.TOP, BorderLineStyle.NONE);
				/** 把单元格上边框样式指定虚线 */
				wcfF.setBorder(Border.LEFT, BorderLineStyle.NONE);
				/** 把单元格左边框样式指定虚线 */
				wcfF.setBorder(Border.RIGHT, BorderLineStyle.NONE);
				/** 把单元格右侧边框样式指定为虚线 */
				wcfF.setBorder(Border.BOTTOM, BorderLineStyle.NONE);
				/** 把单元格底边框样式指定虚线 */
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return wcfF;
	}
}
